create procedure [dbo].[BAEOrderProductGetFeaturedProductsByCategory] @IsFeatured int,
     @OrderCategoryID int as
IF @IsFeatured = 1
    BEGIN
        SELECT    opcl.OrderProductID, op.Title, op.Description, op.IsSuperProduct, op.ProductCode, op.SellOnWeb, SortOrder
        FROM    OrderProductCategoryLookup opcl, OrderProduct op
        WHERE    opcl.OrderCategoryID = @OrderCategoryID AND
            opcl.IsFeatured = 1 AND
            op.OrderProductID = opcl.OrderProductID AND
            op.SellOnWeb != 0 AND
            IsSuperProduct = 1
        UNION        
        SELECT    opcl.OrderProductID, op.Title, op.Description, op.IsSuperProduct, op.ProductCode, op.SellOnWeb, SortOrder
        FROM    OrderProductCategoryLookup opcl, OrderProduct op, Product p
        WHERE    opcl.OrderCategoryID = @OrderCategoryID AND
            opcl.IsFeatured = 1 AND
            op.OrderProductID = opcl.OrderProductID AND
            p.WEB_OPTION  != 0 AND
            IsSuperProduct = 0 AND
            p.PRODUCT_CODE COLLATE database_default = op.ProductCode COLLATE database_default
        ORDER BY SortOrder;
    END
    ELSE
    BEGIN
        SELECT    opcl.OrderProductID, op.Title, op.Description, op.IsSuperProduct, op.ProductCode, op.SellOnWeb, SortOrder
        FROM    OrderProductCategoryLookup opcl, OrderProduct op
        WHERE    opcl.OrderCategoryID = @OrderCategoryID AND
            opcl.IsFeatured = 0 AND
            op.OrderProductID = opcl.OrderProductID AND
            op.SellOnWeb != 0
        UNION        
        SELECT    opcl.OrderProductID, op.Title, op.Description, op.IsSuperProduct, op.ProductCode, op.SellOnWeb, SortOrder
        FROM        OrderProductCategoryLookup opcl, OrderProduct op, Product p
        WHERE    opcl.OrderCategoryID = @OrderCategoryID AND
            opcl.IsFeatured = 0 AND
            op.OrderProductID = opcl.OrderProductID AND
            p.WEB_OPTION  != 0 AND
            IsSuperProduct = 0 AND
            p.PRODUCT_CODE COLLATE database_default = op.ProductCode COLLATE database_default
        ORDER BY SortOrder
    END

GO